//
//  CrystalMinds SQLiteDatabase Library
//  Version 2.3
//
//  Created by Mark van Wijnen on 16-10-09.
//  Last updated on 26-10-09.
//  Copyright 2009 CrystalMinds. All rights reserved.
//  Mail: info@crystalminds.nl
//
// This work is licensed under the Creative Commons Attribution-Noncommercial- 
// No Derivative Works 3.0 Unported License.
// To view this license go to http://creativecommons.org/licenses/by-nc-nd/3.0/ 
// or send a letter to Creative Commons, 171 Second Street, Suite 300, 
// San Francisco, California, 94105, VS.

#import "CMSQLiteDatabase.h"
#import "CMSQLiteDatabaseStatement.h"

#define kSharedDatabaseFilename @"SharedDatabase.sql"

static CMSQLiteDatabase *theSharedDatabase;

@implementation CMSQLiteDatabase

@synthesize lastInsertRowId;
@synthesize lastQuery;
@synthesize maxAmountOfCachedStatements;


+ (CMSQLiteDatabase *)sharedDatabase {
	if (theSharedDatabase == nil) theSharedDatabase = [[CMSQLiteDatabase alloc] initWithFilename:kSharedDatabaseFilename];
	return theSharedDatabase;
}

- (id)init {	
	if (self = [super init]) {
		// Reset error
		if (_error != nil) {
			[_error release];
			_error = nil;
		}	
		
		// Define cached statements
		_cachedStatements = [[NSMutableDictionary alloc] init];
		
		// Define max cached statements
		maxAmountOfCachedStatements = 3;
	}
	return self;
}

- (id)initWithFilename:(NSString *)filename {
	// Define filename
	_filenameOfDatabase = [filename retain];
	
	if (self = [self init]) {				
		// Define the path to the database
		NSArray *pathToDocumentDirectory = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
		NSString *documentDirectory      = [pathToDocumentDirectory objectAtIndex:0];
		_pathToDatabase                  = [[documentDirectory stringByAppendingPathComponent:filename] retain];
		
		// Define file manager
		NSFileManager *fileManager = [NSFileManager defaultManager];
		
		// Determine rather the database already exists
		BOOL databaseExists = [fileManager fileExistsAtPath:_pathToDatabase];
		
		// If the database does not exist, we need to create it first
		if (!databaseExists) {
			// Define the path to the database within the bundle
			NSString *pathToDatabaseInBundle = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:filename];
			
			// Determine rather the database exists within the bundle
			BOOL databaseExistsInBundle = [fileManager fileExistsAtPath:pathToDatabaseInBundle];
			
			if (databaseExistsInBundle) {
				// If the database exists within the bundle, we use that database file
				[fileManager copyItemAtPath:pathToDatabaseInBundle toPath:_pathToDatabase error:&_error];
			} else {
				// If the databse does not exist within the bundle, we need to create a bank file
				NSString *databaseContent = @"";
				[databaseContent writeToFile:_pathToDatabase atomically:YES encoding:NSUTF8StringEncoding error:&_error];
			}
		}
		
		// Abort on error
		if (_error) return self;
		
		// Opening a new database connection
		NSInteger openResult     = sqlite3_open([_pathToDatabase UTF8String], &_databaseHandle);
		NSError *openResultError = [self errorFromCode:openResult];
		if (openResultError != nil) _error = [openResultError retain];
	}
	return self;
}

- (void)dealloc {
	// Release memory for cached statements
	[self clearCachedStatements];
	
	// Close the sqlite database
	if (_databaseHandle != nil) {
		sqlite3_close(_databaseHandle);
	}
	
	// Release memory
	if (_error != nil)              [_error release];
	if (_filenameOfDatabase != nil) [_filenameOfDatabase release];
	if (_pathToDatabase != nil)     [_pathToDatabase release];
	if (_cachedStatements != nil)   [_cachedStatements release];
	if (lastQuery != nil)           [lastQuery release];
	
	[super dealloc];
}

- (NSArray *)allColumnsSchemaForTable:(NSString *)tableName {	
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Define return value
	NSMutableArray *allColumns = [[[NSMutableArray alloc] init] autorelease];
	
	// Table name can not be nil
	if (tableName == nil) return allColumns;
	
	// Define query
	NSString *sql = @"PRAGMA table_info(%@)";
	
    // Define results
	NSArray *results = [self resultsForQuery:[NSString stringWithFormat:sql, tableName]];
	
	// Loop through the results
	for (NSDictionary *columnInfo in results) {
		// Define column schema
		NSMutableDictionary *columnSchema = [[NSMutableDictionary alloc] init];
		
		// Set values
		[columnSchema setObject:[columnInfo objectForKey:@"cid"] forKey:@"index"];
		[columnSchema setObject:[columnInfo objectForKey:@"dflt_value"] forKey:@"defaultValue"];
		[columnSchema setObject:[columnInfo objectForKey:@"name"] forKey:@"name"];
		[columnSchema setObject:[columnInfo objectForKey:@"notnull"] forKey:@"notNull"];
		[columnSchema setObject:[columnInfo objectForKey:@"pk"] forKey:@"primaryKey"];
		[columnSchema setObject:[columnInfo objectForKey:@"type"] forKey:@"type"];
		
		// Add to all columns
		[allColumns addObject:columnSchema];
		
		// Release memory
		[columnSchema release];
	}
	
	// Return all columns
	return allColumns;
}

- (NSArray *)allColumnsForTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Define return value
	NSMutableArray *allColumns = [[[NSMutableArray alloc] init] autorelease];
	
	// Table name can not be nil
	if (tableName == nil) return allColumns;
	
	// Define query
	NSString *sql = @"PRAGMA table_info(%@)";
	
    // Define results
	NSArray *results = [self resultsForQuery:[NSString stringWithFormat:sql, tableName]];
	
	// Define primary key found
	BOOL primaryKeyFound = NO;
	
	// Loop through the results
	for (NSDictionary *columnInfo in results) {
		// Add column to all columns
		[allColumns addObject:[columnInfo objectForKey:@"name"]];
		
		// Determine if a primary key is found
		NSNumber *primaryKey = [columnInfo objectForKey:@"pk"];
		if ([primaryKey intValue] == 1) {
			primaryKeyFound = YES;
		}
	}
	
	// Add rowid if no alias has been found
	if (primaryKeyFound == NO) {
		[allColumns insertObject:@"rowid" atIndex:0];
	}
	
	// Return all the columns
	return allColumns;
}

- (NSArray *)allTables {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Define return value
	NSMutableArray *allTables = [[[NSMutableArray alloc] init] autorelease];
	
	// Define query
	NSString *sql = @"SELECT [name] FROM sqlite_master WHERE [type] = 'table'";
	
	// Define results
	NSArray *results = [self resultsForQuery:sql];
	
	// Loop through the results
	for (NSDictionary *row in results) {
		[allTables addObject:[row objectForKey:@"name"]];
	}
	
	// Return all tables
	return allTables;
}

- (BOOL)columnExists:(NSString *)columnName inTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return NO;
	
	// Lowercase table name
	tableName = [tableName lowercaseString];
	
	// Lowercase column name
	columnName = [columnName lowercaseString];
	
	// Define all columns
	NSArray *allColumns = [self allColumnsForTable:tableName];
	
	// Loop through the columns
	for (NSString *currentColumnName in allColumns) {
		// Lowercase current column
		currentColumnName = [currentColumnName lowercaseString];
		
		// Compare
		if ([currentColumnName isEqualToString:columnName]) return YES;
	}
	
	return NO;
}

- (NSInteger)clearCachedStatements {
	// Define amount of cached statements
	NSInteger amountOfCachedStatements = [_cachedStatements count];
	
	// Clear cached statement
	[_cachedStatements removeAllObjects];
	
	return amountOfCachedStatements;
}

- (NSInteger)clearDatabase {	
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Define changes
	NSInteger changes = 0;
	
	// Define query
	NSString *sql = @"DROP TABLE %@";
	
	// Define all tables
	NSArray *allTables = [self allTables];
		
	// Loop through the tables
	for (NSString *tableName in allTables) {
		changes += [self query:[NSString stringWithFormat:sql, tableName]];
	}	
	
	// Return the number of rows modified 
	return changes;
}

- (NSInteger)clearTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
	
	// Define query
	NSString *sql = @"DELETE FROM %@";
	
	// Execute query
	return [self query:[NSString stringWithFormat:sql, tableName]];
}

- (NSInteger)deleteColumn:(NSString *)columnName inTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
	
	// Column name can not be nil
	if (columnName == nil) return 0;
	
	// Define backup table name
	NSString *backupTableName = @"__DROPPEDCOLUMNTABLE__";
	
	// Define range
	NSRange range;
	
	// Define columns
	NSArray *columns = [self allColumnsSchemaForTable:tableName];
	
	// Define query
	NSString *sql = @"CREATE TABLE %@ (%@)";
	
	// Define fixed column structure
	NSMutableString *columnStructureFull = [[[NSMutableString alloc] initWithString:@""] autorelease];
	NSMutableString *columnStructure     = [[[NSMutableString alloc] initWithString:@""] autorelease]; 
	
	for (NSDictionary *currentColumnStructure in columns) {
		if (![[currentColumnStructure objectForKey:@"name"] isEqualToString:columnName]) [columnStructureFull appendFormat:@"%@ %@, ", [currentColumnStructure objectForKey:@"name"], [currentColumnStructure objectForKey:@"type"]];
		if (![[currentColumnStructure objectForKey:@"name"] isEqualToString:columnName]) [columnStructure appendFormat:@"%@, ", [currentColumnStructure objectForKey:@"name"]];
	}
	if ([columnStructureFull length] > 0) {
		range.length   = 2;
		range.location = [columnStructureFull length] - range.length;
		[columnStructureFull deleteCharactersInRange:range];
	}
	if ([columnStructure length] > 0) {
		range.length   = 2;
		range.location = [columnStructure length] - range.length;
		[columnStructure deleteCharactersInRange:range];
	}
	
	// Execute query
	[self query:[NSString stringWithFormat:sql, backupTableName, columnStructureFull]];
	
	// Abort on error
	if ([self error] != nil) return 0;
	
	// Redefine sql
	sql = @"INSERT INTO %@ SELECT %@ FROM %@";
	
	// Execute query
	[self query:[NSString stringWithFormat:sql, backupTableName, columnStructure, tableName]];
	
	// Abort on error
	if ([self error] != nil) return 0;
	
	// Delete original table
	[self deleteTable:tableName];
	
	// Rename backup table
	[self renameTable:backupTableName to:tableName];
	
	// Abort on error
	if ([self error] != nil) return 0;
	
	return 1;
}

- (NSInteger)deleteRowsinTable:(NSString *)table {
	return [self clearTable:table];
}

- (NSInteger)deleteRowWhereColumn:(NSString *)columnName isEqualTo:(id)object inTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
		
	// Define query
	NSString *sql = @"DELETE FROM %@%@";
	
	// Define range
	NSRange range;
	range.length = 2;
		
	// Define string for where clause
	NSMutableString *stringForWhereClause = [[[NSMutableString alloc] initWithString:@""] autorelease];
	if (columnName != nil && object != nil) {
		[stringForWhereClause appendFormat:@" WHERE %@ = ?", columnName];
	}
	
	// Define arguments
	NSArray *arguments = nil;
	if ([stringForWhereClause length] > 0) arguments = [NSArray arrayWithObject:object];
	
	// Execute query
	return [self query:[NSString stringWithFormat:sql, tableName, stringForWhereClause] arguments:arguments];
}

- (NSInteger)deleteTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
	
	// Define query
	NSString *sql = @"DROP TABLE %@";
	
	// Execute query
	return [self query:[NSString stringWithFormat:sql, tableName]];
}


- (NSString *)description {
	// Define return value
	NSMutableString *databaseSchema = [[[NSMutableString alloc] initWithString:@""] autorelease];
	
	// Add object
	[databaseSchema appendFormat:@"%@\n", [super description]];
	
	// Add database
	[databaseSchema appendFormat:@"Database \"%@\"\n", _filenameOfDatabase];
	
	// Receive all the tables in the database
	NSArray *allTables = [self allTables];
	
	// Loop through the tables
	for (NSString *tableName in allTables) {
		// Add table
		[databaseSchema appendFormat:@"\t%@", tableName];
		 
		// Define columns
		NSArray *columns = [self allColumnsSchemaForTable:tableName];
		
		// Define string for column structure
		NSMutableString *stringForColumnStructure = [[[NSMutableString alloc] initWithString:@""] autorelease];
		
		// Loop through the columns
		for (NSDictionary *column in columns) {
			[stringForColumnStructure appendFormat:@"%@ %@, ", [column objectForKey:@"name"], [column objectForKey:@"type"]];
		}
		if ([stringForColumnStructure length] > 0) {
			NSRange aRange;
			aRange.length   = 2;
			aRange.location = [stringForColumnStructure length] - aRange.length;
			[stringForColumnStructure deleteCharactersInRange:aRange];
		}		
		
		// Add columns
		[databaseSchema appendFormat:@"\t[%@]\n", stringForColumnStructure];
		
		// Define rows
		NSArray *rows = [self rowsOfTable:tableName];
		
		// Loop through the rows
		for (NSDictionary *row in rows) {
			// Define string for row
			NSMutableString *stringForRow = [[[NSMutableString alloc] initWithString:@""] autorelease];
			
			// Loop through the columns
			for (NSDictionary *column in columns) {
				[stringForRow appendFormat:@"%@, ", [row objectForKey:[column objectForKey:@"name"]]];
			}
			if ([stringForRow length] > 0) {
				NSRange anotherRange;
				anotherRange.length   = 2;
				anotherRange.location = [stringForRow length] - anotherRange.length;
				[stringForRow deleteCharactersInRange:anotherRange];
			}
			
			// Add rows
			[databaseSchema appendFormat:@"\t\t%@\n", stringForRow];
		}
	}
	
	// Return database schema
	return databaseSchema;
}


- (NSError *)error {
	return _error;
}

- (NSError *)errorFromCode:(NSInteger)errorCode {
	// Do not continue if the error code is not within error range
	if (errorCode < 1 || errorCode > 26) return nil;
	
	// Define the error description
	NSString *localizedDescription;
	
	if (errorCode == SQLITE_ERROR) {
		localizedDescription = @"SQL error or missing database";
	} else if (errorCode == SQLITE_INTERNAL) {
		localizedDescription = @"Internal logic error in SQLite";
	} else if (errorCode == SQLITE_PERM) {
		localizedDescription = @"Access permission denied";
	} else if (errorCode == SQLITE_ABORT) {
		localizedDescription = @"Callback routine requested an abort";
	} else if (errorCode == SQLITE_BUSY) {
		localizedDescription = @"The database file is locked";
	} else if (errorCode == SQLITE_LOCKED) {
		localizedDescription = @"A table in the database is locked";
	} else if (errorCode == SQLITE_NOMEM) {
		localizedDescription = @"A malloc() failed";
	} else if (errorCode == SQLITE_READONLY) {
		localizedDescription = @"Attempt to write a readonly database";
	} else if (errorCode == SQLITE_INTERRUPT) {
		localizedDescription = @"Operation terminated by sqlite3_interrupt()";
	} else if (errorCode == SQLITE_IOERR) {
		localizedDescription = @"Some kind of disk I/O error occurred";
	} else if (errorCode == SQLITE_CORRUPT) {
		localizedDescription = @"The database disk image is malformed";
	} else if (errorCode == SQLITE_NOTFOUND) {
		localizedDescription = @"NOT USED. Table or record not found";
	} else if (errorCode == SQLITE_FULL) {
		localizedDescription = @"Insertion failed because database is full";
	} else if (errorCode == SQLITE_CANTOPEN) {
		localizedDescription = @"Unable to open the database file";
	} else if (errorCode == SQLITE_PROTOCOL) {
		localizedDescription = @"NOT USED. Database lock protocol error";
	} else if (errorCode == SQLITE_EMPTY) {
		localizedDescription = @"Database is empty";
	} else if (errorCode == SQLITE_SCHEMA) {
		localizedDescription = @"The database schema changed";
	} else if (errorCode == SQLITE_TOOBIG) {
		localizedDescription = @"String or BLOB exceeds size limit";
	} else if (errorCode == SQLITE_CONSTRAINT) {
		localizedDescription = @"Abort due to constraint violation";
	} else if (errorCode == SQLITE_MISMATCH) {
		localizedDescription = @"Data type mismatch";
	} else if (errorCode == SQLITE_MISUSE) {
		localizedDescription = @"Library used incorrectly";
	} else if (errorCode == SQLITE_NOLFS) {
		localizedDescription = @"Uses OS features not supported on host";
	} else if (errorCode == SQLITE_AUTH) {
		localizedDescription = @"Authorization denied";
	} else if (errorCode == SQLITE_FORMAT) {
		localizedDescription = @"Auxiliary database format error";
	} else if (errorCode == SQLITE_RANGE) {
		localizedDescription = @"2nd parameter to sqlite3_bind out of range";
	} else if (errorCode == SQLITE_NOTADB) {
		localizedDescription = @"File opened that is not a database file";
	} else {
		localizedDescription = @"";
	}
	
	// Define failure reason
	NSString *localizedFailureReason = [NSString stringWithUTF8String:(const char *)sqlite3_errmsg(_databaseHandle)];
	
	// Define user info
	NSMutableDictionary *userInfo = [[[NSMutableDictionary alloc] init] autorelease];
	[userInfo setObject:localizedDescription forKey:NSLocalizedDescriptionKey];
	[userInfo setObject:localizedFailureReason forKey:NSLocalizedFailureReasonErrorKey];
	
	// Return the error object
	return [NSError errorWithDomain:@"NSSQLiteErrorDomain" code:errorCode userInfo:userInfo];
}

- (NSInteger)insertColumns:(NSDictionary *)columns intoTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
	
	// Columns can not be nil
	if (columns == nil) return 0;
	if ([[columns allKeys] count] == 0) return 0;
	
	// Define changes
	NSInteger changes = 0;
	
	// Define query 
	NSString *sql = @"ALTER TABLE %@ ADD %@ %@";
	
	// Loop through the columns
	for (NSString *columnName in [columns allKeys]) {
		// Define column type
		NSString *columnType = [columns objectForKey:columnName];
		
		// Execute query
		[self query:[NSString stringWithFormat:sql, tableName, columnName, columnType]];
		if (![self error]) changes++;
	}

	return changes;
}

- (NSInteger)insertRow:(NSDictionary *)row intoTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
	
	// Define query
	NSString *sql = @"INSERT INTO %@ (%@) VALUES (%@)";
	
	// Define range
	NSRange range;
	range.length = 2;
	
	// Define string for columns
	NSMutableString *stringForColumns = [[[NSMutableString alloc] initWithString:@""] autorelease];
	if (row != nil) {
		for (NSString *column in [row allKeys]) {
			[stringForColumns appendFormat:@"%@, ", column];
		}
		if ([stringForColumns length] > 0) {
			range.location = [stringForColumns length] - range.length;
			[stringForColumns deleteCharactersInRange:range];
		}
	} else {
		stringForColumns = [NSMutableString stringWithString:@"rowid"];
	}
	
	// Define string for place holders
	NSMutableString *stringForPlaceholders = [[[NSMutableString alloc] initWithString:@""] autorelease];
	if (row != nil) {
		for (NSString *column in [row allKeys]) {
			[stringForPlaceholders appendFormat:@"?, ", column];
		}
		if ([stringForPlaceholders length] > 0) {
			range.location = [stringForPlaceholders length] - range.length;
			[stringForPlaceholders deleteCharactersInRange:range];
		}
	} else {
		stringForPlaceholders = [NSMutableString stringWithString:@"NULL"];
	}
	
	// Define arguments
	NSArray *arguments = nil;
	if (row != nil) arguments = [row allValues];

	// Execute query
	return [self query:[NSString stringWithFormat:sql, tableName, stringForColumns, stringForPlaceholders] arguments:arguments];
}

- (NSInteger)insertTable:(NSString *)tableName {
	return [self insertTable:tableName withColumns:nil];
}

- (NSInteger)insertTable:(NSString *)tableName withColumns:(NSDictionary *)columns {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
	
	// Define query
	NSString *sql = @"CREATE TABLE %@ (rowid INTEGER PRIMARY KEY%@)";
	
	// Define column description
	NSMutableString *stringForColumns = [[[NSMutableString alloc] initWithString:@""] autorelease];
	if (columns != nil) {
		for (NSString *columnName in [columns allKeys]) {
			NSString *columnType = [columns objectForKey:columnName];
			
			if ([columnType isEqualToString:CMSQLiteDatabaseDataTypeNull] ||
				[columnType isEqualToString:CMSQLiteDatabaseDataTypeInteger] ||
				[columnType isEqualToString:CMSQLiteDatabaseDataTypeFloat] ||
				[columnType isEqualToString:CMSQLiteDatabaseDataTypeString] ||
				[columnType isEqualToString:CMSQLiteDatabaseDataTypeObject]) {
				
				[stringForColumns appendFormat:@",%@ %@", columnName, columnType];
			}
		}
	}
	
	// Execute query
	return [self query:[NSString stringWithFormat:sql, tableName, stringForColumns]];
}

- (NSInteger)renameTable:(NSString *)tableName to:(NSString *)newTableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
	
	// New Table name can not be nil
	if (newTableName == nil) return 0;
	
	// Define query 
	NSString *sql = @"ALTER TABLE %@ RENAME TO %@";
		
	// Execute query
	return [self query:[NSString stringWithFormat:sql, tableName, newTableName]];
}

- (NSArray *)resultsForQuery:(NSString *)sql {
	return [self resultsForQuery:sql arguments:nil];
}

- (NSArray *)resultsForQuery:(NSString *)sql arguments:(NSArray *)arguments {	
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Reset last query
	if (lastQuery != nil) {
		[lastQuery release];
		lastQuery = nil;
	}
	
	// Re-Define last query
	lastQuery = [sql copy];
	
	// Define results
	NSMutableArray *results = [[[NSMutableArray alloc] init] autorelease];
	
	// SQL can not be nil
	if (sql == nil) {
		return results;
	}
		
	// Retrieve cached statement
	CMSQLiteDatabaseStatement *statement = [_cachedStatements objectForKey:sql];

	// Determine rather a cached statement has been found
	if (statement == nil) {
		// No cached statement found
		// Define SQLite statement
		sqlite3_stmt *sqliteStatement;
		
		// Define SQL for SQLite
		const char *sqliteSQL = [sql UTF8String];
		
		// Compile the statement
		NSInteger prepareResult     = (int)sqlite3_prepare_v2(_databaseHandle, sqliteSQL, -1, &sqliteStatement, NULL);
		NSError *prepareResultError = [self errorFromCode:prepareResult];

		if (!prepareResultError) {
			if (maxAmountOfCachedStatements != 0) {
				// Determine rather the maximum amount of cached statements is reached			
				if (maxAmountOfCachedStatements > 0 && maxAmountOfCachedStatements == [_cachedStatements count]) {
					// Define enumerator
					NSEnumerator *enumerator = [_cachedStatements objectEnumerator];
					
					// Define current statement
					CMSQLiteDatabaseStatement *currentStatement;
					
					// Define use count
					NSInteger useCount = 0;
					
					// Define query
					NSString *sqlOfStatement = nil;
					
					// Loop through the statements
					while ((currentStatement = [enumerator nextObject])) {
						if (sqlOfStatement == nil || [currentStatement useCount] < useCount) {
							useCount       = [currentStatement useCount];
							sqlOfStatement = [currentStatement sql];
						}
					}
					
					// Remove statement from cache
					if (sqlOfStatement != nil) [_cachedStatements removeObjectForKey:sqlOfStatement];
				}
				
				// Cache new statement
				statement = [[[CMSQLiteDatabaseStatement alloc] initWithStatement:sqliteStatement forDatabase:_databaseHandle] autorelease];
				[_cachedStatements setObject:statement forKey:sql];
			}
		}
	} else {
		// Increase use count
		[statement used];
		
		// Reset statement
		[statement reset];
	}
	
	// Binding values
	if (statement != nil) {		
		// See if there are arguments to bind
		if (arguments != nil && [arguments isKindOfClass:[NSArray class]]) {
			// Loop through the arguments
			for (NSInteger argumentIndex = 0; argumentIndex < [arguments count]; argumentIndex++) {
				// Define argument object
				id argumentObject = [arguments objectAtIndex:argumentIndex];
				
				// Define result
				NSInteger bindingResult = -1;
				
				// Support for: NULL
				if ([argumentObject isKindOfClass:[NSNull class]]) {
					bindingResult = sqlite3_bind_null([statement statement], (argumentIndex + 1));
				}
				
				// Support for: NSString
				if ([argumentObject isKindOfClass:[NSString class]]) {
					NSString *argumentString = (NSString *)argumentObject;
					bindingResult = sqlite3_bind_text([statement statement], (argumentIndex + 1), [argumentString UTF8String], -1, SQLITE_TRANSIENT);
				}
				
				// Support for: NSNumber (Integer, Float, Double)
				if ([argumentObject isKindOfClass:[NSNumber class]]) {
					NSNumber *argumentNumber = (NSNumber *)argumentObject;
					bindingResult = sqlite3_bind_double([statement statement], (argumentIndex + 1), [argumentNumber doubleValue]);
				}
				
				// Support for: UIImage
				if ([argumentObject isKindOfClass:[UIImage class]]) {
					UIImage *argumentImage         = (UIImage *)argumentObject;
					NSData *imagePNGRepresentation = UIImagePNGRepresentation(argumentImage);
					NSDictionary *imageInfo        = [NSDictionary dictionaryWithObjectsAndKeys:imagePNGRepresentation, @"UIImagePNGRepresentation", @"UIImage", @"sqlite3_bind_image", nil];
					
					NSMutableData *imagePNGRepresentationArchived = [[[NSMutableData alloc] init] autorelease];
					NSKeyedArchiver *archiver                     = [[NSKeyedArchiver alloc] initForWritingWithMutableData:imagePNGRepresentationArchived];
					[archiver encodeObject:imageInfo forKey:@"sqlite3_bind_blob"];
					[archiver finishEncoding];
					[archiver release];
					
					bindingResult = sqlite3_bind_blob([statement statement], (argumentIndex + 1), [imagePNGRepresentationArchived bytes], [imagePNGRepresentationArchived length], NULL);
				}
				
				// Support for: Objects that conform to the NSCoding protocol (NSDate, NSArray, NSDictionary, NSData, etc.)
				if (bindingResult == -1 && [argumentObject conformsToProtocol:@protocol(NSCoding)]) {
					NSMutableData *argumentDataArchived = [[[NSMutableData alloc] init] autorelease];
					NSKeyedArchiver *archiver           = [[NSKeyedArchiver alloc] initForWritingWithMutableData:argumentDataArchived];
					[archiver encodeObject:argumentObject forKey:@"sqlite3_bind_blob"];
					[archiver finishEncoding];
					[archiver release];
					
					bindingResult = sqlite3_bind_blob([statement statement], (argumentIndex + 1), [argumentDataArchived bytes], [argumentDataArchived length], NULL);
				}
				
				// Unsupported
				if (bindingResult != SQLITE_OK) {
					sqlite3_bind_null([statement statement], (argumentIndex + 1));
				}
			}
		}
	}
	
	// Retrieve results
	if (statement != nil) {	
		while (sqlite3_step([statement statement]) == SQLITE_ROW) {
			// Define results
			NSMutableDictionary *row = [[NSMutableDictionary alloc] init];
			
			// Loop through the columns
			NSInteger amountOfColumns = (int)sqlite3_column_count([statement statement]);
			
			for (NSInteger columnIndex = 0; columnIndex < amountOfColumns; columnIndex++) {
				// Define sqlite column name
				const char *sqliteColumnName = (const char *)sqlite3_column_name([statement statement], columnIndex);
				
				// Define column name
				NSString *columnName;
				if (sqliteColumnName) {
					columnName = [NSString stringWithUTF8String:sqliteColumnName];
				} else {
					columnName = @"(null)";
				}
				
				// Define data type of column
				NSInteger dataType = (int)sqlite3_column_type([statement statement], columnIndex);
				
				// Define data value
				id dataValue = nil;
				
				// Support for: NULL
				if (dataType == SQLITE_NULL) {
					dataValue = [NSNull null];
				}
				
				// Support for: Integer
				if (dataType == SQLITE_INTEGER) {
					NSInteger sqliteValue = (int)sqlite3_column_int([statement statement], columnIndex);
					dataValue = [NSNumber numberWithInt:sqliteValue];
				}
				
				// Support for: Float
				if (dataType == SQLITE_FLOAT) {
					CGFloat sqliteValue = (float)sqlite3_column_double([statement statement], columnIndex);
					dataValue = [NSNumber numberWithFloat:sqliteValue];
				}
				
				// Support for: Text
				if (dataType == SQLITE_TEXT) {
					const char *sqliteValue = (const char *)sqlite3_column_text([statement statement], columnIndex);
					
					if (sqliteValue) {
						dataValue = [NSString stringWithUTF8String:sqliteValue];
					} else {
						dataValue = @"(null)";
					}
				}
				
				// Support for: Blob
				if (dataType == SQLITE_BLOB) {
					const void *sqliteBlob = (const void *)sqlite3_column_blob([statement statement], columnIndex);
					NSInteger sqliteBytes  = (int)sqlite3_column_bytes([statement statement], columnIndex);
					
					NSData *valueData;
					
					if (sqliteBlob) {
						valueData = [[[NSData alloc] initWithBytes:sqliteBlob length:sqliteBytes] autorelease];
					} else {
						valueData = [NSData data];
					}
					
					NSKeyedUnarchiver *unarchiver = [[NSKeyedUnarchiver alloc] initForReadingWithData:valueData];
					id unarchivedObject           = [unarchiver decodeObjectForKey:@"sqlite3_bind_blob"];
					[unarchiver finishDecoding];
					[unarchiver release];
					
					if (unarchivedObject == nil) unarchivedObject = @"(null)";
					
					UIImage *image = nil;
					if ([unarchivedObject isKindOfClass:[NSDictionary class]]) {
						NSDictionary *imageInfo = (NSDictionary *)unarchivedObject;
						id objectForKey         = [imageInfo objectForKey:@"sqlite3_bind_image"];
						
						if (objectForKey != nil) {
							id imagePNGRepresentation = [imageInfo objectForKey:@"UIImagePNGRepresentation"];
							
							if (imagePNGRepresentation != nil && [imagePNGRepresentation isKindOfClass:[NSData class]]) {
								image = [UIImage imageWithData:imagePNGRepresentation];
							}
						}
					}
					
					if (image != nil) {
						dataValue = image;
					} else {
						dataValue = unarchivedObject;
					}
				}
				
				// Add data value to row
				if (columnName != nil && dataValue != nil) {
					[row setObject:dataValue forKey:columnName];
				}
			}
			
			// Add row to results
			[results addObject:row];
			
			// Release memory
			[row release];
		}
	}
	
	// Re-Define last inserted row id
	lastInsertRowId = (int)sqlite3_last_insert_rowid(_databaseHandle);
	
	// Return results
	return results;	
}

- (NSDictionary *)rowForQuery:(NSString *)sql rowOffset:(NSInteger)rowOffset {
	return [self rowForQuery:sql arguments:nil rowOffset:rowOffset];
}

- (NSDictionary *)rowForQuery:(NSString *)sql arguments:(NSArray *)arguments rowOffset:(NSInteger)rowOffset {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// SQL can not be nil
	if (sql == nil) return 0;
	
	// Execute the query
	NSArray *results = [self resultsForQuery:sql arguments:arguments];
	
	// Return the row
	@try {
		return [results objectAtIndex:rowOffset];
	}
	@catch (NSException * e) {}
	
	return nil;
}

- (NSArray *)rowsOfTable:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return [NSArray array];
	
	// Define query
	NSString *sql = @"SELECT * FROM %@";
	
	// Execute query
	return [self resultsForQuery:[NSString stringWithFormat:sql, tableName]];
}

- (void)setMaxAmountOfCachedStatements:(NSInteger)amountOfCachedStatements {
	// Define new maximum amount of cached statements
	maxAmountOfCachedStatements = amountOfCachedStatements;
	
	// Clear the current cache
	[self clearCachedStatements];
}

- (BOOL)tableExists:(NSString *)tableName {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return NO;
	
	// Lowercase table name
	tableName = [tableName lowercaseString];
	
	// Define all tables
	NSArray *allTables = [self allTables];
	
	// Loop through the tables
	for (NSString *currentTableName in allTables) {
		// Lowercase current table name
		currentTableName = [currentTableName lowercaseString];
		
		// Compare
		if ([currentTableName isEqualToString:tableName]) return YES;
	}
	
	return NO;
}

- (NSInteger)query:(NSString *)sql {
	return [self query:sql arguments:nil];
}

- (NSInteger)query:(NSString *)sql arguments:(NSArray *)arguments {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// SQL can not be nil
	if (sql == nil) return 0;
	
	// Execute the query
	[self resultsForQuery:sql arguments:arguments];
	
	// Return the number of rows modified
	return (int)sqlite3_changes(_databaseHandle);
}

- (NSInteger)updateRow:(NSDictionary *)row inTable:(NSString *)tableName autoInsert:(BOOL)autoInsert {
	return [self updateRow:row whereColumn:nil isEqualTo:nil inTable:tableName autoInsert:autoInsert];
}

- (NSInteger)updateRow:(NSDictionary *)row whereColumn:(NSString *)column isEqualTo:(id)object inTable:(NSString *)tableName autoInsert:(BOOL)autoInsert {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// Table name can not be nil
	if (tableName == nil) return 0;
	
	// Values can not be nil
	if (row == nil) return 0;
	if ([[row allKeys] count] == 0) return 0;
	
	// Define query
	NSString *sql = @"UPDATE %@ SET %@%@";

	// Define range
	NSRange range;
	range.length = 2;
	
	// Define string for columns data
	NSMutableString *stringForColumnsData = [[[NSMutableString alloc] initWithString:@""] autorelease];
	for (NSString *currentColumn in [row allKeys]) {
		[stringForColumnsData appendFormat:@"%@ = ?, ", currentColumn];
	}
	if ([stringForColumnsData length] > 0) {
		range.location = [stringForColumnsData length] - range.length;
		[stringForColumnsData deleteCharactersInRange:range];
	}
	
	// Define string for where clause
	NSMutableString *stringForWhereClause = [[[NSMutableString alloc] initWithString:@""] autorelease];
	if (column != nil && object != nil) {
		[stringForWhereClause appendFormat:@" WHERE %@ = ?", column];
	}
	
	// Define arguments
	NSMutableArray *arguments = [NSMutableArray arrayWithArray:[row allValues]];
	if ([stringForWhereClause length] > 0) [arguments addObject:object];

	// Execute query
	NSInteger changes = [self query:[NSString stringWithFormat:sql, tableName, stringForColumnsData, stringForWhereClause] arguments:arguments];
	
	// Determine if the row should be inserted
	if ([self error] == nil && changes == 0 && autoInsert == YES) {
		changes = [self insertRow:row intoTable:tableName];
	}
	
	return changes;
}

- (id)variableForQuery:(NSString *)sql column:(NSString *)columnName rowOffset:(NSInteger)rowOffset {
	return [self variableForQuery:sql arguments:nil column:columnName rowOffset:rowOffset];
}

- (id)variableForQuery:(NSString *)sql arguments:(NSArray *)arguments column:(NSString *)columnName rowOffset:(NSInteger)rowOffset {
	// Reset error
	if (_error != nil) {
		[_error release];
		_error = nil;
	}
	
	// SQL can not be nil
	if (sql == nil) return 0;
	
	// Execute the query
	NSArray *results = [self resultsForQuery:sql arguments:arguments];
	
	// Return the variable
	@try {
		NSDictionary *row = [results objectAtIndex:rowOffset];
		return [row objectForKey:columnName];
	}
	@catch (NSException * e) {}
	
	return nil;
}

@end
